/*
 * Created on 2004-01-07
 */
package org.opencube.data.oracle;

import java.io.IOException;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;
import oracle.sql.BLOB;
import oracle.sql.CLOB;

import org.apache.log4j.Logger;
import org.opencube.data.BoundVariable;
import org.opencube.data.Database;
import org.opencube.data.Record;
import org.opencube.data.RecordSet;
import org.opencube.util.ParameterMetaData;
import org.opencube.util.services.logging.StatementLogger;

/**
 * @author <a href="mailto:maciek@fingo.pl">FINGO - Maciej Mroczko</a>
 * TODO: comment
 */
public class Oracle extends Database
{
	private static final long serialVersionUID = 4316594266687281378L;
	private Logger logger = Logger.getLogger( Oracle.class );
	public static int MINIMUM_BATCH_SIZE = 5;
	public static int PREFERRED_BATCH_SIZE = 20;
	/* The driver name */
	private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
	/* The connection test query */
	private static final String TEST_QUERY = "SELECT 1 FROM DUAL";
	/* The special sql type constant = 1111 */
	private static final int ORACLE_SQL_TYPE_SPECIAL = 1111;
	/* The special sql type name constant: CLOB = 'CLOB' */
	private static final String ORACLE_TYPE_NAME_CLOB = "CLOB";
	/* The special sql type name constant: NCLOB = 'NCLOB' */
	private static final String ORACLE_TYPE_NAME_NCLOB = "NCLOB";
	/* The special sql type name constant: NVARCHAR2 = 'NVARCHAR2' */
	private static final String ORACLE_TYPE_NAME_NVARCHAR2 = "NVARCHAR2";
	/* The oracle type name */
	private static final String DATABASE_TYPE = "oracle";

	public Oracle()
	{
		
	}
	
	public Oracle( DataSource dataSource )
	{
		super( dataSource );
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#getType()
	 */
	public String getType()
	{
		return DATABASE_TYPE;
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#getDriverName()
	 */
	protected String getDriverName()
	{
		return DRIVER_NAME;
	}

	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#getTestQuery()
	 */
	protected String getTestQuery()
	{
		return TEST_QUERY;
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#bindInputParameter(java.sql.PreparedStatement, java.lang.String, int, java.lang.Object)
	 */
	protected void bindInputParameter( PreparedStatement ps, String type, int index, Object value ) throws SQLException
	{
		try
		{
			OraclePreparedStatement ops = (OraclePreparedStatement) ps;
			if( TYPE_CLOB.equals( type ) )
			{
				if( value == null )
				{
					ops.setNull( index, OracleTypes.CLOB );
				}
				else
				{
					ops.setClob( index, getCLOB( ops.getConnection(), (String) value ) );
				}
			}
			else if( TYPE_BLOB.equals( type ) )
			{
				if( value == null )
				{
					ops.setNull( index, OracleTypes.BLOB );
				}
				else
				{
					ops.setBlob( index, getBLOB( ops.getConnection(), (byte[]) value ) );
				}
			}
			else
			{
				if( value == null )
				{
					if( TYPE_INTEGER.equals( type ) )
					{
						ops.setNull( index, OracleTypes.DECIMAL );
					}
					else if( TYPE_STRING.equals( type ) )
					{
						ops.setNull( index, OracleTypes.VARCHAR );
					}
					else
					{
						ops.setNull( index, OracleTypes.OTHER );
					}
				}
				else
				{
					ops.setObject( index, value );
				}
			}
		}
		catch( IOException e )
		{
			throw new SQLException( "IO exception occured while binding input parameter: " + e.getMessage() );
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#registerOutputParameter(java.sql.CallableStatement, java.lang.String, int)
	 */
	protected void registerOutputParameter( CallableStatement cs, String type, int index ) throws SQLException
	{
		OracleCallableStatement ocs = (OracleCallableStatement) cs;
		if( TYPE_BOOLEAN.equals( type ) )
		{
			ocs.registerOutParameter( index, OracleTypes.BOOLEAN );
		}
		else if( TYPE_CURSOR.equals( type ) )
		{
			ocs.registerOutParameter( index, OracleTypes.CURSOR );
		}
		else if( TYPE_STRING.equals( type ) )
		{
			ocs.registerOutParameter( index, OracleTypes.VARCHAR );
		}
		else if( TYPE_INTEGER.equals( type ) )
		{
			ocs.registerOutParameter( index, OracleTypes.INTEGER );
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.opencube.oms.core.Database#fetchRecordSet(java.sql.CallableStatement, int)
	 */
	protected RecordSet fetchRecordSet( CallableStatement cs, int index ) throws SQLException
	{
		OracleCallableStatement ocs = (OracleCallableStatement) cs;
		logger.debug( "Get the cursor frm the callable statement" );
		ResultSet rs = ( OracleResultSet ) ocs.getCursor( index );
		logger.debug( "Cursor got" );
		RecordSet result = fetchRecordSet( rs );
		rs.close();
		return result;
	}  

	/**
	 * Maps the current record in the given result set to the hash map using the 
	 * given meta data info.
	 * 
	 * @param resultSet the result set to map
	 * @param columnCount the count of the column in the result set
	 * @param clobs the array of flag if the column at index+1 is clob
	 * @param blobs the array of flag if the column at index+1 is blob
	 * 
	 * @return Record representation of the current record in the given result set.
	 * 
	 * @throws SQLException
	 */
	protected Record mapRecord( ResultSet resultSet, int columnCount, boolean[] clobs, boolean[] blobs ) throws SQLException
	{
		OracleResultSet rs = (OracleResultSet) resultSet;
		Record result = new Record( columnCount );
		for( int i = 1; i <= columnCount; i++ )
		{
			Object value = null;
			if( clobs[ i - 1 ] )
			{
				CLOB clob = rs.getCLOB( i );
				if( clob != null )
				{
					value = clob.getSubString( 1, ( int ) clob.length() );
				}
			}
			else if( blobs[ i - 1 ] ) 
			{
				BLOB blob = rs.getBLOB( i );
				if( blob != null )
				{
					value = blob.getBytes( 1, ( int )blob.length() );
				}
			}
			else
			{
				value = rs.getObject( i );
			}
			result.setValue( i - 1, rs.wasNull() ? null : value );
		}
		return result;
	}
	/*
	 * This method uses temporary clob to create the CLOB object.
	 */
	private CLOB getCLOB( Connection cn, String clobData ) throws SQLException, IOException
	{
		CLOB tempClob = null;
		CallableStatement stmt = null;
		try
		{
			stmt = cn.prepareCall("{ call DBMS_LOB.CREATETEMPORARY(?, TRUE)}");
			stmt.registerOutParameter(1, OracleTypes.CLOB);
			stmt.execute();
			tempClob = (CLOB)stmt.getObject(1);
			Writer w = tempClob.getCharacterOutputStream();
			w.write( clobData );
			w.close();
		} 
		finally 
		{
			if( stmt != null ) 
			{
				try {stmt.close();} catch (Throwable e) { /* We can do nothing more */ }
			}
		}
		return tempClob;

	}

	/*
	 * This method uses temporary blob to create the BLOB object.
	 */
	private BLOB getBLOB( Connection cn, byte[] blobData ) throws SQLException, IOException
	{
		BLOB tempBlob = null;
		CallableStatement stmt = null;
		try
		{
			stmt = cn.prepareCall("{ call DBMS_LOB.CREATETEMPORARY(?, TRUE)}");
			stmt.registerOutParameter(1, OracleTypes.BLOB);
			stmt.execute();
			tempBlob = (BLOB)stmt.getObject(1);
			OutputStream os = tempBlob.getBinaryOutputStream();
			os.write( blobData );
			os.close();
			
		} 
		finally 
		{
			if( stmt != null ) 
			{
				try {stmt.close();} catch (Throwable e) { /* We can do nothing more */ }
			}
		}
		return tempBlob;
	}		
	protected RecordSet fetchRecordSet( ResultSet rs ) throws SQLException
	{
		logger.debug( "Start to fetch result set as record set" );
		ResultSetMetaData metaData = rs.getMetaData();
		 
		RecordSet recordSet = new RecordSet();
		recordSet.setMetaData( getMetaData( metaData ) );
		
		int columnCount = metaData.getColumnCount();
		logger.debug( "Column count in records: " + columnCount );

		boolean[] clobs = new boolean[ columnCount ]; 
		boolean[] blobs = new boolean[ columnCount ]; 
		
		for( int i = 1; i <= columnCount; i++ )
		{
			int type = metaData.getColumnType( i );
			String typeName = metaData.getColumnTypeName( i );
//			String name = metaData.getColumnName( i );
			if( ( type == ORACLE_SQL_TYPE_SPECIAL || type == 2005 )&& !( typeName.equalsIgnoreCase( ORACLE_TYPE_NAME_NVARCHAR2 ) ) )
			{
				if( typeName.equalsIgnoreCase( ORACLE_TYPE_NAME_CLOB ) || typeName.equalsIgnoreCase( ORACLE_TYPE_NAME_NCLOB ) )
				{
					clobs[ i - 1 ] = true;
				}
				else 
				{
					blobs[ i - 1 ] = true;
				}
			}
		}
		int counter = 0;
		while( rs.next() )
		{
			recordSet.addRecord( mapRecord( rs, columnCount, clobs, blobs ) );
			if( ++counter % 5000 == 0 )
			{
				logger.debug( counter + " records converted into record set" );
			}
		}
		logger.debug( "Result set converted into record set" );
		return recordSet;
	}
	
	public int getMinimumBatchSize() throws Exception
	{
		return MINIMUM_BATCH_SIZE;
	}
	
	public int executeBatch( String statement, org.opencube.data.BoundVariable[][] boundVariables) 
	throws Exception
	{
		return executeBatch( statement, boundVariables, null, null );
	}

	public boolean supportsBatching() throws Exception
	{
		return true;
	}

	/* (non-Javadoc)
	 * @see org.opencube.data.DataSource#executeBatch(java.lang.String, org.opencube.data.BoundVariable[][], java.lang.String, java.lang.String)
	 */
	@Override
	public int executeBatch( String statement, BoundVariable[][] boundVariables, String namespace, String resourceKey ) throws SQLException
	{
		try
		{
			// parse
			long parseDuration = System.nanoTime();
			OracleCallableStatement ops = (OracleCallableStatement) getConnection().prepareCall( statement );
			parseDuration = System.nanoTime() - parseDuration;
			
			// execute
			long executionDuration = System.nanoTime();
			ops.setExecuteBatch( PREFERRED_BATCH_SIZE );
			for (int i = 0; i < boundVariables.length; i++)
			{
				bindInputParameters( ops, new ParameterMetaData( statement, boundVariables[ i ] ) );
				ops.executeUpdate();
			}
			ops.sendBatch();

			executionDuration = System.nanoTime() - executionDuration;
			ops.close();
			// log batch execution
			StatementLogger.logBatchStatement( namespace, resourceKey, statement, boundVariables, executionDuration, parseDuration );
			
			return boundVariables.length;
		}
		finally
		{	
			releaseConnection( false );
		}
	}
	
}
